#include "serialportsqldb.h"

// 数据库操作类
SerialportSqldb::SerialportSqldb()
{

}

SerialportSqldb::~SerialportSqldb()
{

}

//打开连接
bool SerialportSqldb::reOpenSql()
{
    QString str = SERIALPORTCONFIG_INSTANCE->searchSystemConfigFile();
    QSettings *settings = new QSettings(str,QSettings::IniFormat);
    settings->setIniCodec(QTextCodec::codecForName("UTF_8"));
    settings->beginGroup("mysqldb");

    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName(settings->value("host").toString());
    db.setPort(settings->value("port").toInt());
    db.setUserName(settings->value("username").toString());
    db.setPassword(settings->value("password").toString());
    databaseName = settings->value("database").toString();
    db.setDatabaseName(databaseName);

    settings->endGroup();
    delete settings;

    if(!db.open()){
        return false;//打开失败
    }
    return true;
}

//关闭连接
bool SerialportSqldb::closeSql()
{
    if(db.isOpen()){
        db.close();
    }
    return true;
}

//错误打印
void SerialportSqldb::errorSql(QString sql)
{
    errorSqlText = sql;
    qCritical("%s",qPrintable(errorSqlText));
}

//执行sql语句，不获取结果
bool SerialportSqldb::queryExec(QString queryStr)
{
    QSqlQuery query(QSqlDatabase::database(databaseName, true));
    if(!query.exec(queryStr))
    {
        errorSql(queryStr);
        return false;
    }
    return  true;
}

//执行sql语句，并获取结果
bool SerialportSqldb::queryExec(QString queryStr,QList<QHash<QString,QString>> &data)
{
    data.clear();
    QSqlQuery query(QSqlDatabase::database(databaseName, true));
    if(!query.exec(queryStr))
    {
        errorSql(queryStr);
        return false;
    }
    QSqlRecord rec = query.record();
    while(query.next())
    {
        QHash<QString,QString> rowData;
        for(int i =0;i<rec.count();i++)
        {
            QVariant::Type ty =  query.value(i).type();
            if( QVariant::Type::Date == ty)
            {
                QDate temp = query.value(i).toDate();
                rowData[rec.fieldName(i)]=temp.toString("yyyy-MM-dd");
            }
            else if( QVariant::Type::Time == ty)
            {
                QTime temp = query.value(i).toTime();
                rowData[rec.fieldName(i)]=temp.toString("hh:mm:ss");
            }
            else if( QVariant::Type::DateTime == ty)
            {
                QDateTime temp = query.value(i).toDateTime();
                rowData[rec.fieldName(i)]=temp.toString("yyyy-MM-dd hh:mm:ss");
            }
            else
                rowData[rec.fieldName(i)]=query.value(i).toString();
        }
        data.append(rowData);
    }
    return  true;
}

//获取数据
bool SerialportSqldb::getData(QString tableName,QHash<QString,QString> &data,QString sqlWhere)
{
    data.clear();
    QList<QHash<QString,QString>> dataList;
    if(!getData(tableName,dataList,sqlWhere))
    {
        return false;
    }
    if(dataList.count() > 0)
    {
        data = dataList[0];
    }
    return true;
}

//获取数据
bool SerialportSqldb::getData(QString tableName,QList<QHash<QString,QString>> &data,QString sqlWhere)
{
    QString queryStr="select * from "+tableName;
    if(!sqlWhere.isEmpty())
        queryStr+=" "+sqlWhere;
    if(!queryExec(queryStr,data))
    {
        return  false;
    }
    return  true;
}

//获取数据
bool SerialportSqldb::getData(QString tableName,QHash<QString,QString> columndata,QList<QHash<QString,QString>> &data,QString sqlWhere)
{
    QString colunmStr;
    if(columndata.count() == 0)
        colunmStr = "*";
    else
    {
        QStringList keys = columndata.keys();
        for(auto key : keys)
        {
            QString column = QString("%1 AS `%2`").arg(key).arg(columndata[key]);
            if(!colunmStr.isEmpty())
                colunmStr += ",";
            colunmStr += column;
        }
    }
    QString queryStr = QString("SELECT %1 FROM %2 %3").arg(colunmStr).arg(tableName).arg( sqlWhere);
    if(!queryExec(queryStr,data))
    {
        return false;
    }
    return true;
}

//增加
bool SerialportSqldb::addData(QString tableName,QHash<QString,QVariant> data)
{
    if(data.isEmpty())
        return false;
    QString queryStr="insert into "+tableName+" ";
    QString fieldStr="(",valueStr="values(";
    QHash<QString,QVariant>::iterator it;
    for ( it = data.begin(); it != data.end(); ++it )
    {
        fieldStr+=it.key()+",";
        valueStr+="'"+it.value().toString()+"',";
    }
    fieldStr=fieldStr.left(fieldStr.length()-1);
    valueStr=valueStr.left(valueStr.length()-1);
    fieldStr+=")";
    valueStr+=")";
    queryStr+=fieldStr+" "+valueStr;
    if(!queryExec(queryStr))
    {
        return false;
    }
    return true;
}

//删除
bool SerialportSqldb::delData(QString tableName, QString sqlWhere)
{
    QString queryStr="delete from "+tableName;
    if(!sqlWhere.isEmpty())
        queryStr+=" "+sqlWhere;
    if(!queryExec(queryStr))
    {
        return false;
    }
    return true;
}

//修改
bool SerialportSqldb::updateData(QString tableName,QHash<QString,QVariant> data,QString sqlWhere)
{
    QString queryStr="update "+tableName+" ";
    QHash<QString,QVariant>::iterator it;
    QString setStr="set ";
    for ( it = data.begin(); it != data.end(); ++it )
    {
        setStr+=it.key()+"='"+it.value().toString()+"'";
        setStr+=",";
    }
    setStr=setStr.left(setStr.length()-1);
    queryStr+=setStr;
    if(!sqlWhere.isEmpty())
        queryStr+=" "+sqlWhere;
    if(!queryExec(queryStr))
    {
        return false;
    }
    return true;
}

bool SerialportSqldb::transaction()
{
    return db.transaction();
}

bool SerialportSqldb::commit()
{
    return db.commit();
}
